<?php
    include_once('ConnectionManager.php');

    function selectAll(){
        $select = "SELECT CAT_ID, CAT_CAT_ID, CAT_NAME, CAT_DESCRIPTION ";
        $from = "FROM CATEGORY C";
        $orderBy = "ORDER BY CAT_NAME ASC";
        ConnectionManager::getInstance()->setQuery($select.$from.$orderBy);
        return ConnectionManager::getInstance()->query();
    }

    function findBy($keyword, $CATEGORYID, $start_position, $row_per_page){
        $select = "SELECT CAT_ID, CAT_CAT_ID, CAT_NAME, CAT_DESCRIPTION, (SELECT COUNT(PRO_ID) FROM PRODUCT P WHERE P.CAT_ID = C.CAT_ID) as nProducts ";
        $from = "FROM CATEGORY C ";
        $where = "";

        if (isset($CATEGORYID) && $CATEGORYID != "") {
            $where .= "WHERE CAT_ID = '$CATEGORYID' ";
        }

        if (isset($keyword)) {
            if(!(isset($CATEGORYID) && $CATEGORYID != "")){
                $where .= "WHERE ";
            } else {
                $where .= "AND ";
            }

            $where .= "CAT_NAME LIKE '%$keyword%' ";
        }

        $where .= "LIMIT " . $start_position . ", " . $row_per_page;
        ConnectionManager::getInstance()->setQuery($select.$from.$where);
        return ConnectionManager::getInstance()->query();
    }

    function countByName($keyword){
        $select = "SELECT CAT_ID ";
        $from = "FROM CATEGORY ";
        $where = "WHERE CAT_NAME LIKE '". $keyword ."'";
        
        ConnectionManager::getInstance()->setQuery($select.$from.$where);
        ConnectionManager::getInstance()->query();
        return ConnectionManager::getInstance()->getNumRows();
    }

    function delete($id){
        $sql = "DELETE FROM CATEGORY WHERE CAT_ID = ". $id;
        ConnectionManager::getInstance()->setQuery($sql);
        return ConnectionManager::getInstance()->query();
    }

    function loadNonParentCATEGORY() {
        $query = "SELECT * FROM  CATEGORY WHERE CAT_ID NOT IN (SELECT DISTINCT CAT_CAT_ID FROM CATEGORY WHERE CAT_CAT_ID IS NOT NULL)";
        return ConnectionManager::getInstance()->query();
    }

    function loadCATEGORYByID($cateID) {
        $query = "SELECT * FROM CATEGORY WHERE CAT_ID = $cateID";
        ConnectionManager::getInstance()->setQuery($query);
        return ConnectionManager::getInstance()->query();
    }
    
    function loadRootCATEGORY(){
        $query = "SELECT * FROM CATEGORY WHERE CAT_CAT_ID IS NULL";
        ConnectionManager::getInstance()->setQuery($query);
        return ConnectionManager::getInstance()->query();
    }
    
    function loadParentCATEGORY() {
        $query = "SELECT * FROM  CATEGORY WHERE CAT_ID IN (SELECT DISTINCT CAT_CAT_ID FROM CATEGORY WHERE CAT_CAT_ID IS NOT NULL)";
        ConnectionManager::getInstance()->setQuery($query);
        return ConnectionManager::getInstance()->query();
    }

    function loadChildCATEGORY($parentID) {
        $query = "SELECT * FROM CATEGORY WHERE CAT_CAT_ID = $parentID";
        ConnectionManager::getInstance()->setQuery($query);
        return ConnectionManager::getInstance()->query();
    }

    function countTotalCATEGORY() {
        $query = "SELECT CAT_ID FROM CATEGORY";
        ConnectionManager::getInstance()->setQuery($query);
        ConnectionManager::getInstance()->query();
        return ConnectionManager::getInstance()->getNumRows();
    }

    function update($catID, $parentID, $name, $description){
        $sql = "UPDATE CATEGORY 
                SET CAT_CAT_ID = $parentID,
                    CAT_NAME = '$name',
                    CAT_DESCRIPTION = '$description'
                WHERE CAT_ID = $catID";
        ConnectionManager::getInstance()->setQuery($sql);
        return ConnectionManager::getInstance()->query();
    }
    
    function insert($parentID, $name, $description){
        $sql = "INSERT INTO CATEGORY (CAT_CAT_ID, CAT_NAME, CAT_DESCRIPTION) 
                VALUES ($parentID, '$name', '$description')";
        ConnectionManager::getInstance()->setQuery($sql);
        return ConnectionManager::getInstance()->query();
    }
 
?>